# -*- coding: utf-8 -*-
import json
import datetime
import pymysql

#测试库人员证书计数


def getEveryDay(begin_date,end_date):
    date_list = []
    begin_date = datetime.datetime.strptime(begin_date, "%Y-%m-%d")
    end_date = datetime.datetime.strptime(end_date,"%Y-%m-%d")
    while begin_date <= end_date:
        date_str = begin_date.strftime("%Y-%m-%d")
        date_list.append(date_str)
        begin_date += datetime.timedelta(days=1)
    return date_list

def get_conn_on_line():
    return pymysql.Connect(host='120.78.173.78',port=3305,db='transfer',user='root',passwd='YiBiao@123456',charset='utf8',cursorclass = pymysql.cursors.DictCursor)



def getDictPersonQualifiList(request):
    result_list = []
    
    request.cursor_standard.execute('''
        select min(cert_dict_id) as qualifi_no,qualifi_type as qualifi_name
        from dict_person_apt
        group by qualifi_type
    ''')
    
    rows = request.cursor_standard.fetchall()
    result_dict = {}
    for row in rows:
        result_dict[str(row['qualifi_no'])] = row['qualifi_name']
    #先填充一级
    for row in rows:
        obj = {}
        obj['id'] = row['qualifi_no']
        obj['state'] = "opened"
        obj['text'] = row['qualifi_name']
        obj['children'] = []
        result_list.append(obj) 
    print(result_list)
    
    return json.dumps(result_list, ensure_ascii=False)

#获取曲线图数据
def getData(request):
    
    start_date = request.get('start_date')
    end_date = request.get('end_date')
    return_obj = {}
    categories = getEveryDay(start_date,end_date)
    testData = []
    onLineData = []
    return_obj['categories']= categories

    request.cursor_33.execute(request.formatSql('''
        select 
            create_date,
            CONVERT(sum(num),SIGNED) total
        from theme.statistics_person_cert 
        where qualifi_type = '${cert_type}'
        and create_date >= '${start_date}'
        and create_date <= '${end_date}'
        group by create_date
    '''))
    rows = request.cursor_33.fetchall()
    dict1 ={}
    for row in rows:
        dict1[str(row['create_date'])] = row['total']

    conn_on_line = get_conn_on_line()
    cursor_on_line = conn_on_line.cursor()
    cursor_on_line.execute(request.formatSql('''
        select 
            create_date,
            CONVERT(sum(num),SIGNED) total
        from transfer.statistics_person_cert 
        where qualifi_type = '${cert_type}'
        and create_date >= '${start_date}'
        and create_date <= '${end_date}'
        group by create_date
    '''))
    rows = cursor_on_line.fetchall()
    dict2 ={}
    for row in rows:
        dict2[str(row['create_date'])] = row['total']
  
    #迭代日期列表  
    for date in categories:
        if date in dict1:
            testData.append(dict1[date])
        else:
            testData.append(0)
     
        if date in dict2:
            onLineData.append(dict2[date])
        else:
            onLineData.append(0)
            
            
    #关闭资源 
    cursor_on_line.close()
    conn_on_line.close()

    return_obj['testData']= testData
    return_obj['onLineData']= onLineData
    return json.dumps(return_obj, ensure_ascii=False)













































